/*
 * Copyright (C) 2009 Dimitrios C. Michalakos
 *
 * This program is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 2 of the License, or
 * (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this program.  If not, see <http://www.gnu.org/licenses/>.
 */
package com.cs.util.db.sql;

import com.cs.util.db.Database;
import com.cs.util.db.ResultSetMetaInfo;
import java.sql.SQLException;

/**
 * MySqlDesigner can be used for designing SQL commands for MySql databases.
 * @author dimitris@jmike.gr
 */
public class MySqlDesigner implements SqlDesigner {

    /**
     * Parses the input parameters and returns a Select SQL command.
     * @param db
     * @param columns
     * @param tables
     * @param joinOperations
     * @param filterBoundaries
     * @param filters
     * @param orders
     * @param pageIndex
     * @param pageSize
     * @return
     * @throws java.sql.SQLException
     */
    public String getSelectSQL(Database db, Column[] columns, String[] tables, Join[] joinOperations, Filter[] filterBoundaries, Filter[] filters, Order[] orders, int pageIndex, int pageSize) throws SQLException {
        final boolean qualified = tables.length > 1;//indicates whether column names should be qualified (i.e. table.column) or not (i.e. column)
        /* start designing sql */
        StringBuilder sql = new StringBuilder();
        /* append SELECT */
        sql.append("SELECT ");
        for (int i = 0; i < columns.length; i++) {//iterate columns
            Column column = columns[i];//get current column
            if (i > 0) {//a column has already been appended
                sql.append(", ");//append comma
            }
            if (qualified) {//column names must be qualified
                sql.append("`" + column.getTable() + "`." + column.getName());//append column
            } else {//column names can be unqualified
                sql.append("`" + column.getName() + "`");//append column
            }
        }
        /* append FROM */
        sql.append(" FROM `" + tables[0] + "`");//append the first table in source
        for (int i = 1; i < tables.length; i++) {//iterate tables starting from the second table in source
            final String table1 = tables[i - 1];
            final String table2 = tables[i];
            switch (joinOperations[i - 1]) {
                case INNER: {
                    sql.append(" INNER JOIN");
                    break;
                }
                case OUTER: {
                    sql.append(" OUTER JOIN");
                    break;
                }
                case LEFT: {
                    sql.append(" LEFT OUTER JOIN");
                    break;
                }
                case RIGHT: {
                    sql.append(" RIGHT OUTER JOIN");
                    break;
                }
            }
            sql.append(" `" + table2 + "`");
            sql.append(" ON");
            String[][] onColumns = db.getDatabaseMetaInfo().getForeignKeyColumns(table1, table2);
            for (int j = 0; j < onColumns.length; j++) {
                sql.append(" `" + table1 + "`." + onColumns[j][0] + " = `" + table2 + "`." + onColumns[j][1]);
                if (j < (onColumns.length - 1)) {
                    sql.append(" AND");
                }
            }
        }
        /* append WHERE */
        if (filters.length != 0 || filterBoundaries.length != 0) {//query contains filter or filterBoundaries
            sql.append(" WHERE ");
            if (filters.length != 0 && filterBoundaries.length > 1) {//query contains filter and more than one filterBoundaries
                sql.append("(");
            }
            for (int i = 0; i < filterBoundaries.length; i++) {//iterate filter boundaries
                Filter filter = filterBoundaries[i];//get current filter boundary
                if (qualified) {//column names must be qualified
                    sql.append("`" + filter.getTable() + "`." + filter.getColumn());
                } else {//column names can be unqualified
                    sql.append("`" + filter.getColumn() + "`");
                }
                sql.append(" = ?");
                if (i < (filterBoundaries.length - 1)) {//more filter boundaries exist
                    sql.append(" AND ");//append AND to sql
                }
            }
            if (filters.length != 0 && filterBoundaries.length > 1) {//query contains filter and more than one filterBoundaries
                sql.append(")");
            }
            if (filters.length != 0 && filterBoundaries.length != 0) {//query contains filter and filterBoundaries
                sql.append(" AND ");//append AND to sql
            }
            if (filters.length > 1 && filterBoundaries.length != 0) {//query contains filterBoundaries and more than one filters
                sql.append("(");
            }
            for (int i = 0; i < filters.length; i++) {//iterate filter
                Filter filter = filters[i];//get current filter
                if (qualified) {//column names must be qualified
                    sql.append("`" + filter.getTable() + "`." + filter.getColumn());
                } else {//column names can be unqualified
                    sql.append("`" + filter.getColumn() + "`");
                }
                sql.append(" = ?");
                if (i < (filters.length - 1)) {//more filters exist
                    sql.append(" AND ");//append comma to sql
                }
            }
            if (filters.length > 1 && filterBoundaries.length != 0) {//query contains filterBoundaries and more than one filters
                sql.append(")");
            }
        }
        /* append ORDER BY */
        if (orders.length != 0) {//query contains order statements
            sql.append(" ORDER BY ");
            for (int i = 0; i < orders.length; i++) {//iterate orders
                Order order = orders[i];//get current order statement
                if (qualified) {//column names must be qualified
                    sql.append("`" + order.getTable() + "`." + order.getColumn());//use table.column format to distinguish the columns
                } else {//column names can be unqualified
                    sql.append("`" + order.getColumn() + "`");
                }
                if (order.isAsc()) {
                    sql.append(" ASC");
                } else {
                    sql.append(" DESC");
                }
                if (i < (orders.length - 1)) {//more orders exist
                    sql.append(", ");//append comma to the sql
                }
            }
        }
        /* append LIMIT */
        sql.append(" LIMIT " + String.valueOf(pageIndex - 1) + "," + String.valueOf(pageSize) + ";");
        /* return String */
        return sql.toString();
    }

    /**
     * Parses the input parameters and returns an Insert SQL command.
     * @param db
     * @param columns
     * @param table
     * @param filterBoundaries
     * @return
     * @throws java.sql.SQLException
     */
    public String getInsertSQL(Database db, String[] columns, String table, Filter[] filterBoundaries) throws SQLException {
        StringBuilder sql = new StringBuilder();
        StringBuilder cs = new StringBuilder();//String buffer for columns part
        StringBuilder vs = new StringBuilder();//String buffer for values part
        ResultSetMetaInfo rsmi = db.getResultSetMetaInfo(table);
        boolean appendComma = false;//indicated whether comma should be appended to string
        for (int j = 0; j < columns.length; j++) {//iterate columns
            String column = columns[j];//get current column
            if (!rsmi.isAutoIncremented(column)) {//current column is not auto incremented
                if (appendComma) {
                    cs.append(", ");
                    vs.append(", ");
                } else {
                    appendComma = true;
                }
                cs.append("`" + column + "`");//append column
                vs.append("?");//append value
            }
        }
        /* design the sql command */
        sql.append("INSERT INTO `" + table + "` (");
        sql.append(cs);
        sql.append(") VALUES (");
        sql.append(vs);
        sql.append(");");
        return sql.toString();
    }
}
